﻿// -----------------------------------------------------------------------
// <copyright file="UserData.cs" company="VDC-IT" author="LTDUC">
//      Copyright (c) VDC-IT. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

namespace VDC.VONE.DAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Reflection;
    using Microsoft.ApplicationBlocks.Data;
    using VDC.COMMON;
    using VDC.VONE.ENTITIES;
    
    /// <summary>
    /// Class access to db User
    /// </summary>
    public class UserData
    {
        /// <summary>
        /// Get User By Id
        /// </summary>
        /// <param name="userId">Param userId</param>
        /// <returns>Return User</returns>
        public UserEN GetUserById(int userId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                UserEN user = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneUsersGetById, userId);
                while (reader.Read())
                {
                    user = new UserEN();

                    if (!reader.IsDBNull(0))
                    {
                        user.UserId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        user.AdministrativeUnitsId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        user.UserName = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        user.FullName = reader.GetString(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        user.Sex = reader.GetBoolean(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        user.BirthDate = reader.GetDateTime(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        user.Email = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        user.Sms = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        user.Phone = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        user.Address = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        user.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        user.Send = reader.GetString(11);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return user;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list all User
        /// </summary>
        /// <returns>Return list all User</returns>
        public IList<UserEN> GetUsers()
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<UserEN> lstUser = new List<UserEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneUsersGetAll);
                while (reader.Read())
                {
                    UserEN user = new UserEN();

                    if (!reader.IsDBNull(0))
                    {
                        user.UserId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        user.AdministrativeUnitsId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        user.UserName = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        user.FullName = reader.GetString(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        user.Sex = reader.GetBoolean(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        user.BirthDate = reader.GetDateTime(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        user.Email = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        user.Sms = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        user.Phone = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        user.Address = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        user.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        user.Send = reader.GetString(11);
                    }

                    lstUser.Add(user);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstUser;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Insert to db User
        /// </summary>
        /// <param name="user">Paarm User</param>
        /// <returns>Insert User</returns>
        public int Insert(UserEN user)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.User.AdministrativeUnitsId, user.AdministrativeUnitsId),
                                    new SqlParameter(Constants.SqlParamerterName.User.UserName, user.UserName),
                                    new SqlParameter(Constants.SqlParamerterName.User.FullName, user.FullName),
                                    new SqlParameter(Constants.SqlParamerterName.User.Sex, user.Sex),
                                    new SqlParameter(Constants.SqlParamerterName.User.BirthDate, (object)user.BirthDate ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.User.Email, user.Email),
                                    new SqlParameter(Constants.SqlParamerterName.User.Sms, user.Sms),
                                    new SqlParameter(Constants.SqlParamerterName.User.Phone, user.Phone),
                                    new SqlParameter(Constants.SqlParamerterName.User.Address, user.Address),
                                    new SqlParameter(Constants.SqlParamerterName.User.CreateDate, (object)user.CreateDate ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.User.Send, string.IsNullOrEmpty(user.Send) ? (object)DBNull.Value : user.Send)
                                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneUsersInsert, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Update to db User
        /// </summary>
        /// <param name="user">Param User</param>
        public void Update(UserEN user)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.User.UserId, user.UserId),
                                    new SqlParameter(Constants.SqlParamerterName.User.AdministrativeUnitsId, user.AdministrativeUnitsId),
                                    new SqlParameter(Constants.SqlParamerterName.User.UserName, user.UserName),
                                    new SqlParameter(Constants.SqlParamerterName.User.FullName, user.FullName),
                                    new SqlParameter(Constants.SqlParamerterName.User.Sex, user.Sex),
                                    new SqlParameter(Constants.SqlParamerterName.User.BirthDate, (object)user.BirthDate ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.User.Email, user.Email),
                                    new SqlParameter(Constants.SqlParamerterName.User.Sms, user.Sms),
                                    new SqlParameter(Constants.SqlParamerterName.User.Phone, user.Phone),
                                    new SqlParameter(Constants.SqlParamerterName.User.Address, user.Address),
                                    new SqlParameter(Constants.SqlParamerterName.User.CreateDate, (object)user.CreateDate ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.User.Send, string.IsNullOrEmpty(user.Send) ? (object)DBNull.Value : user.Send)
                                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneUsersUpdate, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Delete User by userId
        /// </summary>
        /// <param name="userId">param userId</param>
        public void Delete(int userId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.User.UserId, userId)
                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneUsersDelete, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get List UserDepartment By UserId
        /// </summary>
        /// <param name="userId">Param userId</param>
        /// <returns>Return List UserDepartment</returns>
        public IList<UserDepartmentEN> GetUserDepartmentByUserId(int userId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<UserDepartmentEN> lstUser = new List<UserDepartmentEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneUserDepartmentGetByUserId, userId);
                while (reader.Read())
                {
                    UserDepartmentEN userDepartment = new UserDepartmentEN();

                    if (!reader.IsDBNull(0))
                    {
                        userDepartment.DepartmentId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        userDepartment.UserId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        userDepartment.DepartmentId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        userDepartment.PositionsId = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        userDepartment.Active = reader.GetBoolean(4);
                    }

                    lstUser.Add(userDepartment);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstUser;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get User By AdministrativeUnitsId
        /// </summary>
        /// <param name="administrativeUnitsId">Param AdministrativeUnitsId</param>
        /// <returns>Return User</returns>
        public IList<UserEN> GetUserByAdministrativeUnitsId(int administrativeUnitsId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<UserEN> lstUser = new List<UserEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneUsersGetByAdministrativeUnitsId, administrativeUnitsId);
                while (reader.Read())
                {
                    UserEN user = new UserEN();

                    if (!reader.IsDBNull(0))
                    {
                        user.UserId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        user.AdministrativeUnitsId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        user.UserName = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        user.FullName = reader.GetString(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        user.Sex = reader.GetBoolean(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        user.BirthDate = reader.GetDateTime(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        user.Email = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        user.Sms = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        user.Phone = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        user.Address = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        user.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        user.Send = reader.GetString(11);
                    }

                    lstUser.Add(user);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstUser;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets all user by user id.
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <returns>return lstUser;</returns>
        /// <exception cref="System.Exception">SqlException exception</exception>
        public IList<UserEN> GetAllUserByUserId(int userId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<UserEN> lstUser = new List<UserEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneUserGetAllByUserId, userId);
                while (reader.Read())
                {
                    UserEN user = new UserEN();

                    if (!reader.IsDBNull(0))
                    {
                        user.UserId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        user.AdministrativeUnitsId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        user.UserName = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        user.FullName = reader.GetString(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        user.Sex = reader.GetBoolean(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        user.BirthDate = reader.GetDateTime(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        user.Email = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        user.Sms = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        user.Phone = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        user.Address = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        user.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        user.Send = reader.GetString(11);
                    }

                    lstUser.Add(user);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstUser;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the name of the user by user.
        /// </summary>
        /// <param name="userName">Name of the user.</param>
        /// <returns>return UserEN</returns>
        /// <exception cref="System.Exception">
        /// the exc</exception>
        public UserEN GetUserByUserName(string userName)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                UserEN user = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneUsersGetByUserName, userName);
                while (reader.Read())
                {
                    user = new UserEN();

                    if (!reader.IsDBNull(0))
                    {
                        user.UserId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        user.AdministrativeUnitsId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        user.UserName = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        user.FullName = reader.GetString(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        user.Sex = reader.GetBoolean(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        user.BirthDate = reader.GetDateTime(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        user.Email = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        user.Sms = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        user.Phone = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        user.Address = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        user.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        user.Send = reader.GetString(11);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return user;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list users senduser, mainuser, coorditions user
        /// </summary>
        /// <param name="profileId">param profileId</param>
        /// <returns>Return list user</returns>
        public IList<UserEN> GetUsersByProfileId(int profileId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<UserEN> lstUser = new List<UserEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneUsersGetByProfileId, profileId);
                while (reader.Read())
                {
                    UserEN user = new UserEN();

                    if (!reader.IsDBNull(0))
                    {
                        user.UserId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        user.AdministrativeUnitsId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        user.UserName = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        user.FullName = reader.GetString(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        user.Sex = reader.GetBoolean(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        user.BirthDate = reader.GetDateTime(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        user.Email = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        user.Sms = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        user.Phone = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        user.Address = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        user.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        user.Send = reader.GetString(11);
                    }

                    lstUser.Add(user);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstUser;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the user search.
        /// </summary>
        /// <param name="searchUser">The search user.</param>
        /// <returns>return list User</returns>
        public IList<UserEN> GetUserSearch(COMMON.NonPersistents.SearchUserNP searchUser)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.UserSearch.FullName, searchUser.FullName),
                                    new SqlParameter(Constants.SqlParamerterName.UserSearch.UserName, searchUser.UserName),
                                    new SqlParameter(Constants.SqlParamerterName.UserSearch.Position, searchUser.Position),
                                    new SqlParameter(Constants.SqlParamerterName.UserSearch.Department, searchUser.Department)
                                };
                IList<UserEN> lstUser = new List<UserEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneUsersSearch, sqlParameters);
                while (reader.Read())
                {
                    UserEN user = new UserEN();

                    if (!reader.IsDBNull(0))
                    {
                        user.UserId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        user.AdministrativeUnitsId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        user.UserName = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        user.FullName = reader.GetString(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        user.Sex = reader.GetBoolean(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        user.BirthDate = reader.GetDateTime(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        user.Email = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        user.Sms = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        user.Phone = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        user.Address = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        user.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        user.Send = reader.GetString(11);
                    }

                    lstUser.Add(user);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstUser;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Exists the email.
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <param name="email">The email.</param>
        /// <returns>return bool</returns>
        public bool ExistEmail(int userId, string email)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.User.UserId, userId),
                    new SqlParameter(Constants.SqlParamerterName.User.Email, email)
                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneUserExistEmail, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result > 0;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Exists the SMS.
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <param name="sms">The SMS.</param>
        /// <returns>return bool</returns>
        public bool ExistSms(int userId, string sms)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.User.UserId, userId),
                    new SqlParameter(Constants.SqlParamerterName.User.Sms, sms)
                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneUserExistSms, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result > 0;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }
    }
}